-- 末端网点滞留汇总
with terminal_sign_summary_plan_sign as(
     select order_source_name,--订单来源名称
       max(order_source_code) as order_source_code,
       goods_type_code,--物品类型编码
       end_manage_region_code,--末端网点所属管理大区编码
       max(end_manage_region_name) as end_manage_region_name,--末端网点所属管理大区名称
       end_agent_code,--末端网点所属代理区编码
       max(end_agent_name) as end_agent_name,--末端网点所属代理区名称
       max(end_provider_id) as end_provider_id,--末端网点所属省份id
       max(end_provider_name) as end_provider_name,--末端网点所属省份名称
       max(end_city_id) as end_city_id,--末端网点所属城市id
       max(end_city_name) as end_city_name,--末端网点所属城市名称
       max(end_area_id) as end_area_id,--末端网点所属区县id
       max(end_area_name) as end_area_name,--末端网点所属区县名称
       end_center_code,--末端发件中心/集散编码
       max(end_center_name) as end_center_name,--末端发件中心/集散名称
       end_franchisee_code,--末端网点所属加盟商编码
       max(end_franchisee_name) as end_franchisee_name,--末端网点所属加盟商名称
       final_sign_network_code,--最终签收派件网点编码
       max(final_sign_network_name) as final_sign_network_name,--最终签收派件网点名称
       max(planned_delivery_time) as planned_delivery_time,--规划发件时间
       max(deadline_signing_time) as deadline_signing_time,--规划签收截止时间
      -- max(span_days) as span_days,--加时天数
       deliver_shift,--派件派仓班次
       final_sign_user_code,--最后签收派件员编码
       max(final_sign_user_name) as final_sign_user_name,--最后签收派件员名称
        sum(1) as need_sign_count,--应签收汇总
        sum(case when aging_sign_count_type = 25 then 1 else 0 end) as aging_sign_over_time_difficult,--时效签收延误-问题件
        sum(case when aging_sign_count_type = 26 then 1 else 0 end) as aging_sign_over_time_other,--时效签收延误-其他
        final_plan_sign_date,--规划签收日期
        third_code,     --三段码code
        max(third_name) as third_name, -- 三段码名字
       final_plan_sign_date as dt
from (select
         date_format(final_plan_sign_time, 'yyyy-MM-dd') as final_plan_sign_date,--规划签收日期
       case when aging_sign_time is null and is_difficult = 1 then 25
            when aging_sign_time is null then 26
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type = '快件签收' then 11
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type = '代理点收入扫描' then 14
            when aging_sign_time <= final_plan_sign_time and is_aging_sign_pdd = 1 then 12
            when aging_sign_time <= final_plan_sign_time and is_aging_sign_pdd = 2 then 13
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type = '快件签收' then 21
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type = '代理点收入扫描' then 24
            when aging_sign_time > final_plan_sign_time and is_aging_sign_pdd = 1 then 22
            when aging_sign_time > final_plan_sign_time and is_aging_sign_pdd = 2 then 23
            else 26 end aging_sign_count_type,--时效签收统计类型 注：11 准点-网点签收 12 准点-终端入库(桃花岛认证) 13 准点-终端入库(自助对接) 14 准点-代理点收入 21 延误-网点签收 22 延误-终端入库(桃花岛认证) 23 延误-终端入库(自助对接) 24 延误-代理点收入 25 延误-问题件 26 延误-其他
       nvl(order_source_name,'其他') as order_source_name,
       order_source_code,
       nvl(goods_type_code,'bm000006') as goods_type_code,
       billcode,--运单号
       nvl(end_manage_region_code,'-') as end_manage_region_code,--末端网点所属管理大区编码
       nvl(end_manage_region_name,'-') as end_manage_region_name,--末端网点所属管理大区名称
       nvl(end_agent_code,'-') as end_agent_code,--末端网点所属代理区编码
       nvl(end_agent_name,'-') as end_agent_name,--末端网点所属代理区名称
       nvl(end_provider_id,0) as end_provider_id,--末端网点所属省份id
       nvl(end_provider_name,'-') as end_provider_name,--末端网点所属省份名称
       nvl(end_city_id,0) as end_city_id,--末端网点所属城市id
       nvl(end_city_name,'-') as end_city_name,--末端网点所属城市名称
       nvl(end_area_id,0) as end_area_id,--末端网点所属区县id
       nvl(end_area_name,'-') as end_area_name,--末端网点所属区县名称
       nvl(end_franchisee_code,'-') as end_franchisee_code,--末端网点所属加盟商编码
       nvl(end_franchisee_name,'-') as end_franchisee_name,--末端网点所属加盟商名称
       nvl(end_center_code,'-') as end_center_code,--末端发件中心/集散编码
       nvl(end_center_name,'-') as end_center_name,--末端发件中心/集散名称
       nvl(end_center_send_time,'-') as end_center_send_time,--末端中心/集散发件时间
       nvl(final_sign_network_code,'-') as final_sign_network_code,--最终签收派件网点编码
       nvl(final_sign_network_name,'-') as final_sign_network_name,--最终签收派件网点名称
       final_plan_sign_time,--规划签收时间
       planned_delivery_time,--规划发件时间
       deadline_signing_time,--规划签收截止时间
       deliver_shift,--派件派仓班次
       nvl(final_sign_user_code,'-') as final_sign_user_code,--最后签收派件员编码
       nvl(final_sign_user_name,'-') as final_sign_user_name,--最后签收派件员名称
       difficult_first_type,--问题件一级类型
       third_code,   --三段码code
       third_name,  --三段码名字
       dt
from jms_dwd.dwd_terminal_sign_all_detail_new_dt t1
left anti join (
    select waybill_no,max(scan_time) as last_scan_time
    from jms_dwd.dwd_tab_barscan_arrival_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}', -29) and date_add('{{ execution_date | cst_ds }}', 0)
  group by waybill_no
    ) t2
on t1.billcode=t2.waybill_no
and nvl(t1.print_time,'2020-01-01 00:00:00')>end_center_send_time
and t2.last_scan_time>=nvl(t1.print_time,'2049-12-31 23:59:59')
where dt between date_format(date_add('{{ execution_date | cst_ds }}', -35), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
  and to_date(final_plan_sign_time) between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd')
  and is_reject_reback_transfer = 2
  and is_end_piece = 2
  and end_center_send_time <= nvl(actual_sign_time,'9999-99-99')
  ) a
group by final_plan_sign_date,
         order_source_name,
         goods_type_code,
         end_manage_region_code,
         end_agent_code,
         end_center_code,
         end_franchisee_code,
         final_sign_network_code,
         deliver_shift,
         final_sign_user_code,
         third_code
     )
insert overwrite table jms_dm.dm_network_retention_summary_dt partition (dt)
select
        is_contain_difficult  --是否包含问题件 1 是 2 否
       ,end_manage_region_code --末端网点所属管理大区编码
       ,end_manage_region_name  --末端网点所属管理大区名称
       ,end_agent_code  --末端网点所属代理区编码
       ,end_agent_name  --末端网点所属代理区名称
       ,end_provider_id  --末端网点所属省份id
       ,end_provider_name  --末端网点所属省份名称
       ,end_city_id  --末端网点所属城市id
       ,end_city_name  --末端网点所属城市名称
       ,end_area_id    --末端网点所属区县id
       ,end_area_name  --末端网点所属区县名称
       ,end_franchisee_code  -- 末端网点所属加盟商编码
       ,end_franchisee_name  -- 末端网点所属加盟商名称
       ,final_sign_network_code  --最终签收派件网点编码
       ,final_sign_network_name  --最终签收派件网点名称
       ,order_source_name     --订单来源
       ,order_source_code     --订单来源code
       ,need_sign_count_1d  --1天应签收
       ,retention_count_1d  --1天内滞留
       ,need_sign_count_2d  --2天应签收
       ,retention_count_2d  --2天内滞留
       ,need_sign_count_3d  --3天应签收
       ,retention_count_3d  --3天内滞留
       ,need_sign_count_5d  --5天应签收
       ,retention_count_5d  --5天内滞留
       ,need_sign_count_7d  --7天应签收
       ,retention_count_7d  --7天内滞留
       ,need_sign_count_10d --10天应签收
       ,retention_count_10d --10天内滞留
       ,need_sign_count_15d --15天应签收
       ,retention_count_15d --15天内滞留
       ,need_sign_count_20d --20天应签收
       ,retention_count_20d --20天内滞留
       ,final_plan_sign_date  --规划签收日期
       ,third_code  -- 三段码code
       ,third_name  -- 三段码名字
       ,need_sign_over_20d --超20天应签收
       ,retention_over_20d --超20天内滞留

        --家明新加
        ,retention_count_4d  --4天内滞留
        ,need_sign_count_4d  --4天应签收
        ,retention_count_6d  --6天内滞留
        ,need_sign_count_6d  --6天应签收
        ,need_sign_over_7d --超7天应签收
        ,retention_over_7d --超7天内滞留
         --家明新加

       ,final_plan_sign_date as dt
from (
       select '1' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_3d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_4d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_5d,


              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_6d,
              --家明新加4,6滞留和签收


              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -19), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -19), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -20), 'yyyy-MM-dd') then need_sign_count else 0 end) as   need_sign_over_20d, --20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -20), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_over_20d, --20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') then need_sign_count else 0 end) as   need_sign_over_7d, --超7天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_over_7d, --20天内滞留
              --家明新加超7滞留和签收
              date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
       union all
       select '2' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_3d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_4d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_5d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_6d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -19), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -19), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -20), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -20), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_7d, --超20天内滞留
              --家明新加超7滞留和签收

              date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -0), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
    union all
    --2天
     select '1' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_3d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_4d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_5d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_6d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -15), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -15), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -20), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -20), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -21), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -21), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as  retention_over_7d, --超20天内滞留
              --家明新加7滞留和签收

            date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
       union all
       select '2' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_3d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_4d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_5d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_6d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -15), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -15), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -20), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -20), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -21), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -21), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_7d, --超20天内滞留
              --家明新加超7滞留和签收
              date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -25), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -1), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
       union all
    --3天
     select '1' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_3d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_4d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_5d,

              --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_6d,
              --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -11), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -11), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -16), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -16), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -21), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -21), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -22), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -22), 'yyyy-MM-dd') then (aging_sign_over_time_other+ aging_sign_over_time_difficult) else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') then (aging_sign_over_time_other+ aging_sign_over_time_difficult) else 0 end) as  retention_over_7d, --超20天内滞留
              --家明新加超7滞留和签收

              date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
       union all
       select '2' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_3d,

               --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_4d,
               --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_5d,

               --家明新加4,6滞留和签收 #有问题
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_6d,
               --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -11), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -11), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -16), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -16), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -21), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -21), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -22), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -22), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_7d, --超20天内滞留
             --家明新加超7滞留和签收

              date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -26), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -2), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
       union all
    --4天
     select '1' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_3d,

             --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_4d,
               --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_5d,

             --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_6d,
             --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -12), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -12), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -17), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -17), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -22), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -22), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -23), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -23), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as  retention_over_7d, --超20天内滞留
              --家明新加超7滞留和签收

            date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
       union all
       select '2' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_3d,


               --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_4d,
                 --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_5d,

               --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_6d,
             --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -12), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -12), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -17), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -17), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -22), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -22), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -23), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -23), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_7d, --超20天内滞留
              --家明新加超7滞留和签收

              date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -27), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -3), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
    union all
    -- 5天
     select '1' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_3d,

               --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_4d,
               --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_5d,

               --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_6d,
               --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -13), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -13), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -18), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -18), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -23), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -23), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -11), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -11), 'yyyy-MM-dd') then (aging_sign_over_time_other + aging_sign_over_time_difficult) else 0 end) as  retention_over_7d, --超20天内滞留
              --家明新加超7滞留和签收

            date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
       union all
       select '2' as is_contain_difficult,
              max(end_manage_region_code) as end_manage_region_code,
              max(end_manage_region_name) as end_manage_region_name,
              max(end_agent_code) as end_agent_code,
              max(end_agent_name) as end_agent_name,
              max(end_provider_id) as end_provider_id,
              max(end_provider_name) as end_provider_name,
              max(end_city_id) as end_city_id,
              max(end_city_name) as end_city_name,
              max(end_area_id) as end_area_id,
              max(end_area_name) as end_area_name,
              max(end_franchisee_code) as end_franchisee_code,
              max(end_franchisee_name) as end_franchisee_name,
              final_sign_network_code,
              max(final_sign_network_name) as final_sign_network_name,
              order_source_name,
              max(order_source_code) as order_source_code,
              third_code,  --三段码code
              max(third_name) as third_name,  --三段码名字
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_1d,
              sum(case when final_plan_sign_date = date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_1d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -5), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_2d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_3d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -6), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_3d,

               --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_4d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -7), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_4d,
               --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_5d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -8), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_5d,

               --家明新加4,6滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_6d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -9), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_6d,
                 --家明新加4,6滞留和签收

              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -10), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_7d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -13), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -13), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_10d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -18), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -18), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_15d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -23), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then need_sign_count else 0 end) as            need_sign_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -23), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as retention_count_20d,
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_20d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -24), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_20d, --超20天内滞留

              --家明新加超7滞留和签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -11), 'yyyy-MM-dd') then need_sign_count else 0 end) as need_sign_over_7d, --超20天应签收
              sum(case when final_plan_sign_date between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -11), 'yyyy-MM-dd') then aging_sign_over_time_other else 0 end) as  retention_over_7d, --超20天内滞留
              --家明新加超7滞留和签收

              date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd') as final_plan_sign_date
       from terminal_sign_summary_plan_sign
       where dt between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', -4), 'yyyy-MM-dd')
       group by final_sign_network_code,order_source_name,third_code
       ) t
       distribute by dt, abs(hash(final_sign_network_code)) % 20
;